'use strict';

const conn = require('../../libs/Conn');
const fs = require('fs');
const moment = require('moment');
const Excel = require('exceljs');
const _ = require('underscore');
module.exports = async function (params) {
    let buildingUseSql ='';
    let regionSql = '';
    let yearSql = '';
    let year = '所有年度';
     /*年份条件*/
    if(params.nd){
         year = moment(params.nd).format('YYYY');
        let yearBegin = moment(params.nd).format('YYYY-01-01');
        let yearEnd = moment(params.nd).format('YYYY-12-31');
        yearSql = `AND (ef.startDate BETWEEN '${yearBegin}' AND '${yearEnd}') `;
    }

    /*建筑用途条件*/
    if(params.jzyt){
        if(params.jzyt!=='0'){
            buildingUseSql = `AND ef.building_id = '${params.jzyt}'`;
        }
    }

    /*区域条件*/
    let userSql = ``;
    if(params.qy){
        if(params.qy!=='0') {
            regionSql = `AND ef.region_id = '${params.qy}'`;
        }
    }else{
        if(params.is_manager !==1){
            userSql = `AND ef.engineering_id IN (               SELECT
                                                                engineering_id
                                                            FROM
                                                                engineering_file
                                                            WHERE
                                                                principal_id = '${params.user_id}'
                                                            AND delete_sign = 1
                                                            UNION
                                                                SELECT
                                                                    efa.engineering_id
                                                                FROM
                                                                    engineering_user_middle eum
                                                                INNER JOIN engineering_file efa ON efa.engineering_id = eum.engineering_id
                                                                AND efa.delete_sign = 1
                                                                WHERE
                                                                    eum.user_id = '${params.user_id}'
                                                                UNION
                                                                    SELECT
                                                                        epid.engineering_id
                                                                    FROM
                                                                        progress_information_user_middle pium
                                                                    INNER JOIN engineering_progress_information_data epid ON epid.information_id = pium.information_id AND epid.delete_sign = 1
                                                                WHERE  pium.user_id = '${params.user_id}'
                                                            UNION
                                                                    SELECT
                                                                        engineering_id
                                                                    FROM
                                                                        engineering_progress_information_data
                                                                WHERE delete_sign = 1 AND user_id = '${params.user_id}'
                                                            ) `;
        }
    }


    let sql = `SELECT
                    ef.engineering_no,
                    ef.engineering_name,
                    sf.structure_name,
                    rf.region_name,
                    eif.aboveGround_floor,
                    eif.underGround_floor,
                    eif.engineering_area,
                    cue.unit_name AS construction_unit_employer_name,
                    eif.construction_unit_employer_person,
                    dsu.unit_name AS design_unit_name,
                    eif.design_unit_person,
                    cuc.unit_name AS construction_unit_contractor_name,
                    eif.construction_unit_contractor_person,
                    spu.unit_name AS supervisory_unit_name,
                    eif.supervisory_unit_person,
                    svu.unit_name AS survey_unit_name,
                    eif.survey_unit_person,
                    dwu.unit_name AS drawing_unit_name,
                    eif.drawing_unit_person,
                    eif.engineering_address,
                    ef.status
                FROM
                    engineering_file ef
                LEFT JOIN region_file rf ON ef.region_id = rf.region_id   
                LEFT JOIN structure_file sf ON ef.structure_id = sf.structure_id 
                LEFT JOIN engineering_information_file eif ON ef.engineering_id = eif.engineering_id 
                LEFT JOIN unit_file cue ON eif.construction_unit_employer_id = cue.unit_id 
                LEFT JOIN unit_file dsu ON eif.design_unit_id = dsu.unit_id 
                LEFT JOIN unit_file cuc ON eif.construction_unit_contractor_id = cuc.unit_id 
                LEFT JOIN unit_file spu ON eif.supervisory_unit_id = spu.unit_id 
                LEFT JOIN unit_file svu ON eif.survey_unit_id = svu.unit_id 
                LEFT JOIN unit_file dwu ON eif.drawing_unit_id = dwu.unit_id 
                WHERE
                    ef.delete_sign = 1 ${userSql} ${yearSql} ${buildingUseSql} ${regionSql}`;

    let rec = await conn(sql);

    let workbook = new Excel.Workbook();
    workbook.creator = 'test';
    workbook.lastModifiedBy = 'test';
    workbook.created = new Date();
    workbook.modified = new Date();


    let sheet = workbook.addWorksheet(year+'年度报表');
    let title = year+'年度';
    if(params.qy){
                title +=params.qyName;
        }
    if(params.jzyt){
                title +=params.jzytName;
       }
    title +='工程概况一览表';
    sheet.getRow(1).values = [title,,,,,,,,,,,,,,,,,,,];
    sheet.getRow(2).values = ['序号', '工程名称', '结构类型', '层数',, '面积(㎡)', '建设单位',,'监理单位',,'勘察单位',,'设计单位',,'施工单位',,'图审单位',,'工程地址','状态'];
    sheet.getRow(3).values = ['序号', '工程名称', '结构类型', '地上层数','地下层数', '面积(㎡)','名称','负责人','名称','负责人','名称','负责人','名称','负责人','名称','负责人','名称','负责人','工程地址','状态'];


    // 合并单元格
    sheet.mergeCells(`A1:T1`);
    sheet.mergeCells('A2:A3');
    sheet.mergeCells('B2:B3');
    sheet.mergeCells('C2:C3');
    sheet.mergeCells('F2:F3');
    sheet.mergeCells('S2:S3');
    sheet.mergeCells('T2:T3');
    sheet.mergeCells('D2:E2');
    sheet.mergeCells('G2:H2');
    sheet.mergeCells('I2:J2');
    sheet.mergeCells('K2:L2');
    sheet.mergeCells('M2:N2');
    sheet.mergeCells('O2:P2');
    sheet.mergeCells('Q2:R2');
    sheet.columns = [
        {key: 'engineering_no', width: 15},
        {key: 'engineering_name', width: 15},
        {key: 'structure_name', width: 15},
        {key: 'aboveGround_floor', width: 15},
        {key: 'underGround_floor', width: 15},
        {key: 'engineering_area', width: 15},
        {key: 'construction_unit_employer_name', width: 15},
        {key: 'construction_unit_employer_person', width: 15},
        {key: 'design_unit_name', width: 15},
        {key: 'design_unit_person', width: 15},
        {key: 'construction_unit_contractor_name', width: 15},
        {key: 'construction_unit_contractor_person', width: 15},
        {key: 'supervisory_unit_name', width: 15},
        {key: 'supervisory_unit_person', width: 15},
        {key: 'survey_unit_name', width: 15},
        {key: 'survey_unit_person', width: 15},
        {key: 'drawing_unit_name', width: 15},
        {key: 'drawing_unit_person', width: 15},
        {key: 'engineering_address', width: 15},
        {key: 'status', width: 15}

    ];
    let dataList = [];
    _.each(rec,function (data) {
        switch (data.status) {
            case 1:
                data.status = '在建';
                break;
            case 2:
                data.status = '竣工未验收';
                break;
            case 3:
                data.status = '已验收未备案';
                break;
            case 4:
                data.status = '已验收且备案';
                break;
        }
        let obj = {
            engineering_no:data.engineering_no,
            engineering_name:data.engineering_name,
            structure_name:data.structure_name,
            aboveGround_floor:data.aboveGround_floor,
            underGround_floor:data.underGround_floor,
            engineering_area:data.engineering_area,
            construction_unit_employer_name:data.construction_unit_employer_name,
            construction_unit_employer_person:data.construction_unit_employer_person,
            design_unit_name:data.design_unit_name,
            design_unit_person:data.design_unit_person,
            construction_unit_contractor_name:data.construction_unit_contractor_name,
            construction_unit_contractor_person:data.construction_unit_contractor_person,
            supervisory_unit_name:data.supervisory_unit_name,
            supervisory_unit_person:data.supervisory_unit_person,
            survey_unit_name:data.survey_unit_name,
            survey_unit_person:data.survey_unit_person,
            drawing_unit_name:data.drawing_unit_name,
            drawing_unit_person:data.drawing_unit_person,
            engineering_address:data.engineering_address,
            status:data.status,
        };
        dataList.push(obj)
    });

    sheet.addRows(dataList);
    for(let i = 1;i<=dataList.length+3;i++){
        sheet.getCell('A'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('A'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('B'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('B'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('C'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('C'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('D'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('D'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('E'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('E'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('F'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('F'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('G'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('G'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('H'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('H'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('I'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('I'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('J'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('J'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('K'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('K'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('L'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('L'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('M'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('M'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('N'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('N'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('O'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('O'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('P'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('P'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('Q'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('Q'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('R'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('R'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('S'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('S'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
        sheet.getCell('T'+i).alignment = { vertical: 'middle', horizontal: 'center' };
        sheet.getCell('T'+i).border = {top: {style:'thick'},left: {style:'thick', color: {argb:'000'}},bottom: {style:'thick', color: {argb:'000'}}, right: {style:'thick', color: {argb:'000'}}};
    }
    workbook.xlsx.writeFile('export/'+title+'.xlsx');
    return {success:true,fileName:title+'.xlsx'}
    };